package co.fitstart.admin.dao;

import java.util.Date;
import java.util.List;
import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.persistence.annotation.Execute;
import com.demo2do.core.persistence.annotation.Limit;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;
import co.fitstart.admin.entity.report.CoachFinishedSchedule;
import co.fitstart.admin.entity.report.HourlySchedule;
import co.fitstart.entity.coach.CoachRestCalendar;
import co.fitstart.entity.coach.CoachStudentRelation;
import co.fitstart.entity.coach.PrincipalReportRelation;
import co.fitstart.entity.course.CourseType;
import co.fitstart.entity.course.round.CourseRound;
import co.fitstart.entity.course.round.CourseRoundGroup;
import co.fitstart.entity.course.schedule.CourseSchedule;
import co.fitstart.entity.course.schedule.ScheduleSmsNotice;
import co.fitstart.entity.course.schedule.ScheduleStatus;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.site.RoomScheduleRecord;
import co.fitstart.entity.site.RoomType;
import co.fitstart.entity.site.Site;
import co.fitstart.entity.user.Student;

/**
 * 
 * @author lidan
 *
 */
@Dynamic("hibernate")
public interface CourseScheduleDao {
    
    /**
     * 
     * @param beginDate
     * @param endDate
     * @param site
     * @return
     */
    @Query("SELECT new co.fitstart.admin.entity.report.HourlySchedule(cs.occurDate, cs.coach.name, cs.occurHour, cs.courseType, course.experience, course.give) FROM CourseSchedule cs WHERE cs.disabled =false AND cs.occurDate >= :beginDate AND cs.occurDate <= :endDate AND cs.coach.id in (:coachIds) ORDER BY cs.occurDate ASC, cs.occurHour ASC")
    public List<HourlySchedule> listCourseSchedule(@Variable("beginDate")Date beginDate, @Variable("endDate")Date endDate, @Variable("coachIds")List<Long> coachIds);
    
    /**
     * 
     * @param courseRounds
     * @param status
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.scheduleStatus = :status AND courseSchedule.courseRound in (:courseRounds)")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRounds") List<CourseRound> courseRounds, @Variable("status") ScheduleStatus status);
    
    /**
     * 
     * @param scheduleStatus
     * @param dates
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.scheduleStatus = :scheduleStatus AND courseSchedule.occurDate in (:dates) ORDER BY courseSchedule.occurDate DESC, courseSchedule.occurHour DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("dates") List<Date> dates);
    
    /**
     * 
     * @param site
     * @param scheduleStatus
     * @param dates
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.scheduleStatus = :scheduleStatus AND courseSchedule.site = :site AND courseSchedule.occurDate in (:dates) ORDER BY courseSchedule.occurDate DESC, courseSchedule.occurHour DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("site") Site site, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("dates") List<Date> dates);
    
    /**
     * 
     * @param sites
     * @param scheduleStatus
     * @param dates
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.scheduleStatus = :scheduleStatus AND courseSchedule.site IN (:sites) AND courseSchedule.occurDate in (:dates) ORDER BY courseSchedule.occurDate DESC, courseSchedule.occurHour DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("sites") List<Site> sites, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("dates") List<Date> dates);
    
    /**
     * 
     * @param site
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND site = :site")
    public List<CourseSchedule> listCourseSchedule(@Variable("site") Site site);
    
    /**
     * 
     * @param coach
     * @param date
     * @param hour
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :date AND courseSchedule.occurHour = :hour")
    public List<CourseSchedule> listCourseSchedule(@Variable("coach") Principal coach, @Variable("date") Date date, @Variable("hour") String hour);
    
    /**
     * 
     * @param coach
     * @param date
     * @param hours
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :date AND courseSchedule.occurHour in(:hours)")
    public List<CourseSchedule> listCourseSchedule(@Variable("coach") Principal coach, @Variable("date") Date date, @Variable("hours") String[] hours);
    
    /**
     * 
     * @param courseRound
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND occurDate IS NOT NULL ORDER BY occurDate DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRound") CourseRound courseRound);

    /**
     * 
     * @param coach
     * @param date
     * @return
     */
    @Query("SELECT courseSchedule.occurHour FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :date")
    public List<String> listCourseHours(@Variable("coach") Principal coach, @Variable("date") Date date);
    
    /**
     * 
     * @param coach
     * @param date
     * @param hours
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :date AND courseSchedule.occurHour in (:hours)")
    public List<CourseSchedule> listSchedules(@Variable("coach") Principal coach, @Variable("date") Date date, @Variable("hours") List<String> hours);
    
    /**
     * 
     * @param student
     * @param date
     * @param hours
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.student = :student AND courseSchedule.occurDate = :date AND courseSchedule.occurHour in (:hours)")
    public List<CourseSchedule> listSchedules(@Variable("student") Student student, @Variable("date") Date date, @Variable("hours") List<String> hours);
    
    /**
     * 
     * @param courseRounds
     * @param status
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.scheduleStatus in (:status) AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour AND courseSchedule.courseRound in (:courseRounds)")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRounds") List<CourseRound> courseRounds, @Variable("status") List<ScheduleStatus> status, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);
    
    /**
     * 
     * @param courseRounds
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour AND courseSchedule.courseRound in (:courseRounds)")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRounds") List<CourseRound> courseRounds, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);
    
    /**
     * 
     * @param student
     * @param courseRound
     * @param occurDate
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.student = :student AND courseSchedule.occurDate = :occurDate")
    public List<CourseSchedule> listCourseSchedule(@Variable("student") Student student, @Variable("courseRound") CourseRound courseRound, @Variable("occurDate") Date occurDate);
    
    /**
     * 
     * @param student
     * @param courseStatus
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.student = :student AND courseSchedule.scheduleStatus = :courseStatus")
    public List<CourseSchedule> listCourseSchedule(@Variable("student") Student student, @Variable("courseStatus") ScheduleStatus courseStatus, @Variable("courseRound") CourseRound courseRound);
    
    /**
     * 
     * @param student
     * @param courseRound
     * @param startDate
     * @param endDate
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.student = :student AND courseSchedule.occurDate >= :startDate AND courseSchedule.occurDate <= :endDate")
    public List<CourseSchedule> listCourseSchedule(@Variable("student") Student student, @Variable("courseRound") CourseRound courseRound, @Variable("startDate") Date startDate, @Variable("endDate") Date endDate);
    
    /**
     * 
     * @param courseRound
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND occurDate <= :occurDate AND occurHour < :occurHour AND scheduleStatus = 2 ORDER BY occurDate DESC, occurHour DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRound") CourseRound courseRound, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);

    /**
     * 
     * @param coach
     * @param scheduleDays
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND coach = :coach AND occurDate BETWEEN :beginDate AND :endDate")
    public List<CourseSchedule> listCourseSchedule(@Variable("coach") Principal coach, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate);
    
    /**
     * 
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND courseType in (:courseTypes) AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate ORDER BY occurDate ASC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseTypes") CourseType[] courseTypes, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param courseTypes
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("SELECT new co.fitstart.admin.entity.report.CoachFinishedSchedule(coach.name, site.name, course.name, course.id, course.hours, COUNT(*), occurDate) FROM CourseSchedule WHERE disabled = false AND courseType in (:courseTypes) AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate GROUP BY course, coach, occurDate ORDER BY site, occurDate")
    public List<CoachFinishedSchedule> listCoachFinishSchedule(@Variable("courseTypes") CourseType[] courseTypes, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param site
     * @param courseTypes
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("SELECT new co.fitstart.admin.entity.report.CoachFinishedSchedule(coach.name, site.name, course.name, course.id, course.hours, COUNT(*), occurDate) FROM CourseSchedule WHERE disabled = false AND site = :site AND courseType in (:courseTypes) AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate GROUP BY course, coach, occurDate ORDER BY site, occurDate")
    public List<CoachFinishedSchedule> listCoachFinishSchedule(@Variable("site") Site site, @Variable("courseTypes") CourseType[] courseTypes, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param sites
     * @param courseTypes
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("SELECT new co.fitstart.admin.entity.report.CoachFinishedSchedule(coach.name, site.name, course.name, course.id, course.hours, COUNT(*), occurDate) FROM CourseSchedule WHERE disabled = false AND site IN (:sites) AND courseType in (:courseTypes) AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate GROUP BY course, coach, occurDate ORDER BY site, occurDate")
    public List<CoachFinishedSchedule> listCoachFinishSchedule(@Variable("sites") List<Site> sites, @Variable("courseTypes") CourseType[] courseTypes, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param site
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND courseType in (:courseTypes) AND site = :site AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate ORDER BY site, occurDate ASC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseTypes") CourseType[] courseTypes, @Variable("site") Site site, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param courseTypes
     * @param sites
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND courseType in (:courseTypes) AND site IN (:sites) AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate ORDER BY site, occurDate ASC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseTypes") CourseType[] courseTypes, @Variable("sites") List<Site> sites, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param student
     * @param courseRound
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND student = :student ORDER BY occurDate DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("student") Student student, @Variable("courseRound") CourseRound courseRound);
    
    /**
     * 
     * @param student
     * @param date
     * @param hours
     * @param experience
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND student = :student AND occurDate = :date AND occur_hour in(:hours) AND course.experience = :experience")
    public List<CourseSchedule> listCourseSchedule(@Variable("student") Student student, @Variable("date") Date date, @Variable("hours") String[] hours, @Variable("experience") boolean experience);
    
    /**
     * 
     * @param coach
     * @param day
     * @param currentHour
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND course.experience = true AND coach = :coach AND occurDate = :day AND scheduleStatus = 1 AND occurHour >= :currentHour")
    public List<CourseSchedule> listExperienceSchedule(@Variable("coach") Principal coach, @Variable("day") Date day, @Variable("currentHour") String currentHour);
    
    /**
     * 
     * @param coach
     * @param day
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND course.experience = true AND coach = :coach AND occurDate = :day AND scheduleStatus = 1")
    public List<CourseSchedule> listExperienceSchedule(@Variable("coach") Principal coach, @Variable("day") Date day);
    
    /**
     * 
     * @param site
     * @param roomType
     * @param day
     * @param hour
     * @return
     */
    @Query("FROM RoomScheduleRecord roomScheduleRecord WHERE roomScheduleRecord.disabled = false AND roomScheduleRecord.site = :site AND roomScheduleRecord.roomType = :type AND roomScheduleRecord.occurDate = :day AND roomScheduleRecord.occurHour = :hour")
    public List<RoomScheduleRecord> getRoomRemain(@Variable("site") Site site, @Variable("type") RoomType roomType, @Variable("day") Date day, @Variable("hour") String hour);
    
    /**
     * 
     * @param student
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.student = :student AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour")
    public CourseSchedule getCourseSchedule(@Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);
    
    /**
     * 
     * @param id
     * @param student
     * @param occurDate
     * @param courseRound
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND id != :id AND student = :student AND occurDate = :occurDate AND courseRound = :courseRound")
    public CourseSchedule loadCourseSchedule(@Variable("id") Long id, @Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("courseRound") CourseRound courseRound);
    /**
     * 
     * @param roundCode
     * @param occurDate
     * @param occurHour
     */
    @Execute("UPDATE RoomScheduleRecord SET disabled = true WHERE roundCode = :roundCode AND occurDate = :occurDate AND occurHour = :occurHour")
    public void freezeScheduleRecord(@Variable("roundCode") String roundCode, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);
    
    /**
     * 
     * @param coach
     * @param student
     * @param courseType
     * @return
     */
    @Query("FROM CoachStudentRelation coachStudentRelation WHERE coachStudentRelation.courseType = :courseType AND coachStudentRelation.coach = :coach AND  coachStudentRelation.student = :student")
    public CoachStudentRelation loadCoachStudentRelation(@Variable("courseType") CourseType courseType, @Variable("coach") Principal coach, @Variable("student") Student student);
    
    /**
     * 
     * @param courseId
     * @param scheduleStatus
     * @param beginDate
     * @param endDate
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND course.id = :courseId AND site.id = :siteId AND scheduleStatus = :scheduleStatus AND occurDate BETWEEN :beginDate AND :endDate")
    public int getScheduleAmount(@Variable("courseId") Long courseId, @Variable("siteId") Long siteId, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginDate") Date beginDate, @Variable("endDate") Date endDate);

    /**
     * 
     * @param courseRound
     * @param status
     */
    @Execute("UPDATE CourseSchedule SET disabled = true WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus != :status")
    public void updateScheduleStatus(@Variable("courseRound") CourseRound courseRound, @Variable("status") ScheduleStatus status);
    
    /**
     * 
     * @param courseRound
     * @param status
     */
    @Execute("UPDATE CourseSchedule SET disabled = true WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus = :status")
    public void disableSchedule(@Variable("courseRound") CourseRound courseRound, @Variable("status") ScheduleStatus status);
    
    /**
     * list latest course schedule by courseRound and scheduleStatus
     * @param courseRound
     * @param scheduleStatus
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus in ( :scheduleStatus) ORDER BY occurDate DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") List<ScheduleStatus> scheduleStatus, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus = :scheduleStatus")
    public int count(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);
    
    /**
     * list latest course schedule by courseRound and scheduleStatus, except schedule
     * @param courseRound
     * @param scheduleStatus
     * @param schedule
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule schedule WHERE schedule.disabled = false AND schedule.courseRound = :courseRound AND schedule.scheduleStatus in ( :scheduleStatus) AND schedule != :schedule ORDER BY schedule.occurDate DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") List<ScheduleStatus> scheduleStatus, @Variable("schedule") CourseSchedule schedule, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * load CoachRestCalendar
     * @param coach
     * @param restDate
     * @param restHour
     * @return
     */
    @Query("FROM CoachRestCalendar coachRestCalendar WHERE coachRestCalendar.disabled = false AND coachRestCalendar.coach = :coach AND coachRestCalendar.restDate = :restDate AND coachRestCalendar.restHour = :restHour")
    public CoachRestCalendar loadCoachRestCalendar(@Variable("coach")Principal coach, @Variable("restDate")Date restDate, @Variable("restHour")String restHour);
    
    /**
     * list course schedule by courseRound and scheduleStatu
     * @param courseRound
     * @param scheduleStatus
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus = :scheduleStatus ORDER BY id desc")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param courseSchedule
     * @return
     */
    @Query("FROM ScheduleSmsNotice WHERE disabled = false AND courseSchedule = :courseSchedule")
    public ScheduleSmsNotice getScheduleSmsNotice(@Variable("courseSchedule") CourseSchedule courseSchedule);
    
    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus in ( :scheduleStatus) ORDER BY occurDate ASC,occurHour ASC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus[] scheduleStatus);
    
    /**
     * 
     * @param courseRound
     * @return
     */
    @Query("FROM CourseRoundGroup WHERE disabled = false AND courseRound = :courseRound")
    public CourseRoundGroup loadCourseRoundGroup(@Variable("courseRound") CourseRound courseRound);

    /**
     * 训练部位不为空且上课时间最大的课时
     * @param courseRound
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND occurDate IS NOT NULL AND occurHour IS NOT NULL AND content IS NOT NULL AND courseRound = :courseRound ORDER BY occurDate DESC, occurHour DESC")
    public CourseSchedule load(@Variable("courseRound") CourseRound courseRound);
    
    @Query("FROM CourseSchedule WHERE disabled = false AND occurDate IS NOT NULL AND student = :student AND scheduleStatus = :scheduleStatus")
    public CourseSchedule loadFirstFinishSchedule(@Variable("student") Student student, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param courseRound
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND (occurDate < :occurDate OR (occurDate = :occurDate AND occurHour <= :occurHour))")
    public int count(@Variable("courseRound") CourseRound courseRound, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);
    
    /**
     * LZJ
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation")
    public List<PrincipalReportRelation> listAllRelation();
   
    /**
     * LZJ
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE  site = :site")
    public List<PrincipalReportRelation> listSiteCoachIds(@Variable("site") Site site);
    
    /**
     * LZJ
     * @param coachIds
     * @return
     */
    @Query("FROM Principal WHERE  Id  IN (:coachIds) AND disabled = false")
    public List<Principal> getSiteCoachs(@Variable("coachIds")List<Long> coachIds);

}
